package com.artup.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import com.artup.pojo.Order;

/**
 * 订单
 * @author hapday
 * @date 2017年7月18日 @Time 上午9:46:40
 */
@Repository
public interface OrderDao {
	/**
	 * 添加【订单】
	 * @param order 【订单】
	 * @throws Exception
	 */
	@Insert( value = { "INSERT INTO bud_order (db_id, code, user_db_id, created_dt, total, num, fee, discount, channel_code, client, status, address_id, invoice_id)"
			+ " VALUES(#{id, javaType=String, jdbcType=VARCHAR}, #{code, javaType=String, jdbcType=VARCHAR}, #{passportId, javaType=Integer, jdbcType=INTEGER}"
			+ ", NOW(), #{totalPrice, javaType=Float, jdbcType=FLOAT}, #{totalQuantity, javaType=Integer, jdbcType=INTEGER}, #{expressFee, javaType=Float, jdbcType=FLOAT}"
			+ ", #{totalDiscount, javaType=Float, jdbcType=FLOAT}, #{channelCode, javaType=String, jdbcType=VARCHAR}, #{clientCode, javaType=String, jdbcType=VARCHAR}"
			+ ", #{status, javaType=Byte, jdbcType=TINYINT}, #{addressId, javaType=String, jdbcType=VARCHAR}, #{invoiceId, javaType=Long, jdbcType=BIGINT})" } )
	void insertOrder(Order order) throws Exception;
	
	/**
	 * 根据【通行证ID】查询【订单总记录数】
	 * @param passportId 【通行证ID】
	 * @return 【订单列表】
	 * @throws SQLException
	 */
	@Select( value = { "<script>SELECT COUNT(1)"
			+ " FROM bud_order o"
			+ " WHERE o.is_recycle = 2 AND (o.client = 'ios' OR o.client = 'android')"
			+ "<if test='null != code and \"\" != code'>"
				+ " AND o.code = #{code, javaType=String, jdbcType=VARCHAR}"
			+ "</if>"
			+ "<if test='null != mobileCode and \"\" != mobileCode'>"
				+ " AND o.address_id IN (SELECT a.db_id FROM bud_address a WHERE a.mobile = #{mobileCode, javaType=String, jdbcType=VARCHAR})"
			+ "</if>"
			+ "<if test='null != beginCreateTime and \"\" != beginCreateTime'>"
				+ " AND o.created_dt >= #{beginCreateTime, javaType=String, jdbcType=VARCHAR}"
			+ "</if>"
			+ "<if test='null != endCreateTime and \"\" != endCreateTime'>"
				+ " AND o.created_dt >= #{endCreateTime, javaType=String, jdbcType=VARCHAR}"
			+ "</if>"
			+ "<if test='null != passportId and 0 &lt; passportId'>"
				+ " AND o.user_db_id = #{passportId, javaType=Integer, jdbcType=INTEGER}"
			+ "</if>"
			+ "<if test='0 &lt; payStatus and 3 &gt; payStatus'>"
				+ "AND o.db_id IN (SELECT p.order_db_id FROM bud_order_payment p WHERE p.status = #{payStatus, javaType=Byte, jdbcType=TINYINT})"
			+ "</if>"
			+ "<if test='null != status and -5 != status and 0 != status'>"
				+ "<if test='3 == status'>"
					+ " AND status IN (3, 4, 5, 6, 7, 8, 9)"
				+ "</if>"
				+ "<if test='3 != status'>"
					+ " AND status = #{status, javaType=Byte, jdbcType=TINYINT}"
				+ "</if>"
			+ "</if>"
			+ " </script>" } )
	long selectOrderTotalCount(Order order) throws SQLException;

	/**
	 * 根据【通行证ID】查询【订单列表】
	 * @param passportId 【通行证ID】
	 * @return 【订单列表】
	 * @throws SQLException
	 */
	@Select( value = { "<script>SELECT o.db_id AS id, o.code, o.transaction_Id AS transactionCode"
				+ ", o.user_db_id AS passportId, o.total totalPrice, o.discount AS totalDiscount, o.num AS totalQuantity"
				+ ", o.created_dt AS createTime, o.status, o.client AS clientCode, o.address_id AS addressId, o.invoice_id AS invoiceId"
				+ ", o.check_status checkStatus, o.check_dt AS checkTime, o.coupon_code AS couponCode"
			+ " FROM bud_order o"
			+ " WHERE o.is_recycle = 2 AND (o.client = 'ios' OR o.client = 'android')"
			+ "<if test='null != code and \"\" != code'>"
				+ " AND o.code = #{code, javaType=String, jdbcType=VARCHAR}"
			+ "</if>"
			+ "<if test='null != mobileCode and \"\" != mobileCode'>"
				+ " AND o.address_id IN (SELECT a.db_id FROM bud_address a WHERE a.mobile = #{mobileCode, javaType=String, jdbcType=VARCHAR})"
			+ "</if>"
			+ "<if test='null != passportId and 0 &lt; passportId'>"
				+ " AND o.user_db_id = #{passportId, javaType=Integer, jdbcType=INTEGER}"
			+ "</if>"
			+ "<if test='0 &lt; payStatus and 3 &gt; payStatus'>"
				+ "AND o.db_id IN (SELECT p.order_db_id FROM bud_order_payment p WHERE p.status = #{payStatus, javaType=Byte, jdbcType=TINYINT})"
			+ "</if>"
			+ "<if test='null != beginCreateTime and \"\" != beginCreateTime'>"
				+ " AND o.created_dt >= #{beginCreateTime, javaType=String, jdbcType=VARCHAR}"
			+ "</if>"
			+ "<if test='null != endCreateTime and \"\" != endCreateTime'>"
				+ " AND o.created_dt >= #{endCreateTime, javaType=String, jdbcType=VARCHAR}"
			+ "</if>"
			+ "<if test='null != status and -5 != status and 0 != status'>"
				+ "<if test='3 == status'>"
					+ " AND status IN (3, 4, 5, 6, 7, 8, 9)"
				+ "</if>"
				+ "<if test='3 != status'>"
					+ " AND status = #{status, javaType=Byte, jdbcType=TINYINT}"
				+ "</if>"
			+ "</if>"
			+ " ORDER BY created_dt DESC LIMIT #{offset, javaType=Long, jdbcType=BIGINT}, #{pageSize, javaType=Integer, jdbcType=INTEGER}</script>" } )
	List<Order> selectOrderList(Order order) throws SQLException;

	/**
	 * 根据【ID】查询【订单详情】
	 * @param id 【ID】
	 * @return 【订单详情】
	 * @throws SQLException
	 */
	@Select( value = { "SELECT"
			+ "		 o.db_id AS id, o.code, o.created_dt AS createTime, o.total totalPrice, o.num AS totalQuantity, o.fee AS expressFee, o.status, o.user_db_id AS passportId, o.address_id AS addressId, o.invoice_id AS invoiceId"
			+ " FROM bud_order o"
			+ " WHERE o.db_id = #{id, javaType=String, jdbcType=VARCHAR}" } )
	Order selectOrderById(String id) throws SQLException;

	/**
	 * 根据【编号】查询【订单详情】
	 * @param code 【编号】
	 * @return 【订单详情】
	 * @throws SQLException
	 */
	@Select( value = { "SELECT"
			+ "		 o.db_id AS id, o.code, o.created_dt AS createTime, o.total totalPrice, o.fee AS expressFee, o.status, o.user_db_id AS passportId, o.address_id AS addressId, o.invoice_id AS invoiceId"
			+ " FROM bud_order o"
			+ " WHERE o.code = #{code, javaType=String, jdbcType=VARCHAR}" } )
	Order selectOrderByCode(String code) throws SQLException;
	
	
	/**
	 * 根据【通行证ID】查询【订单数】
	 * @param passportId 【通行证ID】
	 * @return 【订单数】
	 * @throws Exception
	 */
	@Select( value = { "SELECT COUNT(1) FROM bud_order o WHERE o.user_db_id = #{passportId}  " } )
	Long selectOrderQuantityByPassportId(String passportId) throws SQLException;
	
	/**
	 * 根据【订单号】更新【订单状态】
	 * @param code 订单号
	 * @param status 订单状态
	 * @throws SQLException
	 */
	@Update( value = { "UPDATE bud_order SET status = #{status, javaType=Byte, jdbcType=TINYINT} WHERE code = #{code, javaType=String, jdbcType=VARCHAR}" } )
	void updateOrderStatus(@Param("code") String code, @Param("status") byte status) throws SQLException;

	/**
	 *  根据【订单号】更新【订单】
	 * @param order
	 * @throws SQLException
	 */
	@Update( value = { "UPDATE bud_order o"
			+ " SET o.status = #{status, javaType=Byte, jdbcType=TINYINT}"
			+ ", o.transaction_Id = #{transactionCode, javaType=String, jdbcType=VARCHAR}"
			+ " WHERE o.code = #{code, javaType=String, jdbcType=VARCHAR}" } )
	void updateOrderByCode(Order order) throws SQLException;

	/**
	 * 根据【订单号】更新【回收状态】
	 * @param isRecyle 回收状态 
	 * @throws SQLException
	 */
	@Update( value = { "UPDATE bud_order SET is_recycle = #{isRecycle, javaType=Byte, jdbcType=TINYINT} WHERE code = #{code, javaType=String, jdbcType=VARCHAR}" } )
	void updateOrderRecycleByCode(@Param("code") String code, @Param("isRecycle") byte isRecycle) throws SQLException;

	/**
	 * 根据【编号】更新【审核状态】
	 * @param code 【编号】
	 * @param checkStatus 【审核状态】
	 * @throws SQLException
	 */
	@Update( value = { "UPDATE bud_order SET check_status = #{checkStatus, javaType=Byte, jdbcType=TINYINT}, check_dt = NOW() WHERE code = #{code, javaType=String, jdbcType=VARCHAR}" } )
	void updateOrderCheckStatusByCode(@Param("code") String code, @Param("checkStatus") byte checkStatus) throws SQLException;

	/**
	 * 根据【ID】更新【审核状态】
	 * @param id 【ID】
	 * @param checkStatus 【审核状态】
	 * @throws SQLException
	 */
	@Update( value = { "UPDATE bud_order SET check_status = #{checkStatus, javaType=Byte, jdbcType=TINYINT}, check_dt = NOW() WHERE db_id = #{id, javaType=String, jdbcType=VARCHAR}" } )
	void updateOrderCheckStatusById(@Param("id") String id, @Param("checkStatus") byte checkStatus) throws SQLException;

	/**
	 * 根据【订单号】查询【订单状态】
	 * @param code 【订单号】
	 * @return 【订单状态】
	 * @throws SQLException
	 */
	@Select( value = { "SELECT o.status"
			+ " FROM bud_order o"
			+ " WHERE o.code = #{code, javaType=String, jdbcType=VARCHAR}" } )
	byte selectOrderStatusByCode(String code) throws SQLException;
}
